create database db_Eadministration

go

use db_Eadministration

go

create table [User]
(
	Id varchar(30) primary key,
	Password varchar(30) not null,
	CreateDate datetime,
	Status varchar(15) not null default 'Enable',	-- Enable, Disable
	Name nvarchar(50),
	Sex bit,
	DOB datetime,
	Phone varchar(15),
	Email text,
	Address ntext
)

create table [Role]
(
	Id varchar(15) primary key,		-- Admin, Instructor, Techinical Staff
	Name nvarchar(30) not null,
	Description ntext
)

create table [Group]
(
	Id varchar(15) primary key,
	Name nvarchar(30) not null,
	Description ntext
)

create table Permission
(	
	Id int primary key identity(1,1),
	UserID varchar(30) references [User](id),
	RoleID varchar(15) references [Role](id),
	GroupID varchar(15) references [Group](id)
)

create table Lab
(
	Id varchar(15) primary key,
	Name nvarchar(30) not null,
	CreateDate datetime,
	Description ntext,
	Status varchar(15) not null	default 'Activate'	-- Activate, Deactivate
)

create table TimeTable
(
	Id varchar(15) primary key,		-- 4 khung gio: M1, M2, A1, A2
	StartTime smalldatetime,
	EndTime smalldatetime,
	Description ntext
)

create table SignLab
(
	Id int primary key identity(1,1),
	Signer varchar(30) references [User](id),
	LabID varchar(15) references Lab(id),
	TimeTable varchar(15) references TimeTable(id),
	CreateDate datetime,
	UseDate smalldatetime, -- The day is Requested
	Content ntext,
	Status varchar(15) not null	default 'Request'	-- Request, Deny, Approve, Done
)

create table Device
(
	Id varchar(15) primary key,
	LabID varchar(15) references Lab(id),
	Name nvarchar(30) not null,	
	CreateDate datetime,
	Warranty datetime,
	Description ntext,
	endWarranty bit default 1,
	isProlem bit default 0,
	Status varchar(15) not null	default 'Activate'	-- Activate, Deactivate
)

create table Technical
(
	Id int primary key identity(1,1),
	Technician varchar(30) references [User](id),
	LabID varchar(15) references Lab(id),
	TimeTable varchar(15) references TimeTable(id)
)

create table Report
(
	Id int primary key identity(1,1),
	Sender varchar(30) references [User](id),
	DeviceID varchar(15) references Device(id),
	CreateDate datetime,
	Title ntext,	
	Content ntext,	
	Status varchar(15) not null default 'In Process',		-- In Process, Confirm, Done
	Technician varchar(30) references [User](id),
	EndDate	datetime -- The day is Tecnical Fix done Issue
)